﻿'Hecho por Hernan Servetto
Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports Entidades

Public Class DBPedido
    Public cn As OdbcConnection
    Public con As SqlConnection
    Sub New()
        Try
            cn = New OdbcConnection("UID=pruebasexto" & _
         ";PWD=pruebasexto123" & _
         ";DATABASE=pruebasexto" & _
         ";SERVER=db4free.net" & _
         ";DRIVER={MySQL ODBC 3.51 Driver};")
            cn.Open()
        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("ERror al conectarse")
        End Try


    End Sub
    Function AltaPedido(ByVal nodo As Entidades.Pedido) As Boolean
        Try
            Try
                Dim cmd As OdbcCommand = New OdbcCommand("{call alta_pedido  (?,?,?,?,?,?)}", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@id_prod", nodo.pid0)
                cmd.Parameters.AddWithValue("@cant", nodo.pid01)
                cmd.Parameters.AddWithValue("@id_client", nodo.pid1)
                cmd.Parameters.AddWithValue("@fech", nodo.pid2)
                cmd.Parameters.AddWithValue("@id_reparti", nodo.pid3)
                cmd.Parameters.AddWithValue("@estad", nodo.pid4)

                cn.Open()
                cmd.ExecuteNonQuery()

            Catch o As OdbcException
                MsgBox(o.Message.ToString)
                Return False
            Finally
                cn.Close()
            End Try
            Return True

        Catch ex As Exception


        End Try
        Return False
    End Function
    Function BajaPedido(ByVal nodo As Entidades.Pedido) As Boolean
        Try
            Try
                Dim cmd As OdbcCommand = New OdbcCommand("{call baja_pedido (?)}", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@id", nodo.pid)

                cn.Open()
                cmd.ExecuteNonQuery()

            Catch o As OdbcException
                MsgBox(o.Message.ToString)
                Return False
            Finally
                cn.Close()
            End Try
            Return True

        Catch ex As Exception


        End Try
        Return False

    End Function
    Function ModificarPedido(ByVal nodo As Entidades.Pedido) As Boolean
        Try
            Try
                Dim cmd As OdbcCommand = New OdbcCommand("{call modificacion_pedido (?,?,?,?,?,?)}", cn)
                cmd.Parameters.AddWithValue("@id_prod", nodo.pid0)
                cmd.Parameters.AddWithValue("@cant", nodo.pid01)
                cmd.Parameters.AddWithValue("@id_client", nodo.pid1)
                cmd.Parameters.AddWithValue("@fech", nodo.pid2)
                cmd.Parameters.AddWithValue("@id_reparti", nodo.pid3)
                cmd.Parameters.AddWithValue("@estad", nodo.pid4)

                cn.Open()
                cmd.ExecuteNonQuery()

            Catch o As OdbcException
                MsgBox(o.Message.ToString)
                Return False
            Finally
                cn.Close()
            End Try
            Return True

        Catch ex As Exception


        End Try
        Return False

    End Function
    Function consultapedido() As ArrayList
        ' ARRAYLIST QUE SE CARGARÁ CON LOS DATOS Y SE RETORNA
        Dim arr As ArrayList = New ArrayList
        Dim cmd As OdbcCommand = New OdbcCommand("{call Consulta_pedido ()}", cn)
        ' SE CARGA EL DATAREADER

        Dim dr As OdbcDataReader = cmd.ExecuteReader()
        'RECORRO EL DATAREADER AGREGANDO ITEMS EN EL ARRAYLIST
        ' EL ARRAYLIST GUARDA NODO (CAPSULA), ASI QUE PRIMERO SE CARGA EL NODO Y LUEGO SE AGREGA UN ELEMENTO EN EL ARRAYLIST
        ' 

        While dr.Read 'MIENTRAS HAYA ALGO EN EL DATAREADER
            Dim nodo As Pedido = New Pedido 'CREO EL NODO
            nodo.pid02 = dr.GetInt32(0) 'CARGO EL NODO CON LOS DATOS DEL DATAREADER

            arr.Add(nodo) ' AGREGO UN ELEMENTO AL ARRAYLIST


        End While

        dr.Close()

        Return arr

    End Function
End Class
